<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Moutlet extends CI_Model {

  public function initVars($foo) {
    $this->db->select('lookup_key, lookup_value, lookup_desc');
    $this->db->where_in('lookup_key', $foo);
    $this->db->order_by('lookup_key, order', 'asc');
    return $this->db->get('t_mtr_lookup')->result();
  }

  public function get_province() {
    $this->db->order_by('province_name', 'asc');
    $query = $this->db->get('t_mtr_province');
    return $query->result();
  }

  // Apapun status outletnya, tampilkan datanya dari tabel unapproved. Tapi jika data dari tabel unppproved tidak ada (0 row), 
  // baru tampilkan data dari tabel masternya. Jika tidak ada maka akan digantikan oleh _blankVars()
  public function complements($table, $channel_id) {
    $this->db->where('outlet_id', $channel_id);
    $query = $this->db->get($table."_unapproved");

    if($query->num_rows() > 0) { 
      if($table == 't_mtr_outlet_frontliner' || $table == 't_mtr_bank_account' || $table == 't_mtr_outlet_eload')
        { return $query->result_array(); }
      else 
        { return $query->row_array(); }
    }

    else {
      $this->db->where('outlet_id', $channel_id); 
      $query = $this->db->get($table);
      if($table == 't_mtr_outlet_frontliner' || $table == 't_mtr_bank_account' || $table == 't_mtr_outlet_eload')
        { return $query->result_array(); }
      else 
        { return $query->row_array(); }
    }
  }

  public function complement_mfino($channel_id)
  {
    $this->db->select('ee.eload_number, ce.user_mfino');
    // taken out by anggwa
    // $this->db->join("t_mtr_channel_eload ce", "ce.eload_number = ee.eload_number and ce.channel_id = '$channel_id'" ,"left");
    $this->db->join("t_mtr_channel_eload ce", "ce.eload_number = ee.eload_number" ,"left");
    $this->db->where('ee.outlet_id', $channel_id);
    $query = $this->db->get("t_mtr_outlet_eload_unapproved ee");
    if($query->num_rows() == 0 )
    {
      $this->db->select('ee.eload_number, ce.user_mfino');
      $this->db->join("t_mtr_channel_eload ce", "ce.eload_number = ee.eload_number and ce.channel_id = '$channel_id'" ,"left");
      $this->db->where('ee.outlet_id', $channel_id);
      $query = $this->db->get("t_mtr_outlet_eload ee");
    }
    return $query->result_array();
  }

  public function outletCtegory($outlet_id)
  {
    $this->db->select('category_name');
    $query = $this->db->get_where('t_mtr_channel_category', array('channel_id' => $outlet_id));
    return $query->row();
  }

  public function channel_territory($outlet_id = null, $territory_id = null) {
    $this->db->select("f_get_territory_path(f_get_territory_id_by_channel_id('$outlet_id')) as cluster_name");
    return $this->db->get()->row()->cluster_name;
  }

  public function getTerritory($channel_id, $istatus = null) {
    $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$channel_id')", false);
    // $this->db->where('istatus', $istatus);
    $query = $this->db->get('t_mtr_territory_unapproved');
    if($query->num_rows() < 1) { 
      $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$channel_id')", false);
      $query = $this->db->get('t_mtr_territory'); 
    }
    return $query->row_array();
  }

  public function getPrograms($cluster_id, $program_type) {
    $this->db->select("program_id, caption, description, program_type_id, territory_id");
    $this->db->where_in("program_type_id", $program_type);
    $this->db->where("istatus", 1);
    $this->db->where_in("territory_id", array( $cluster_id, 1 ) );

    $query = $this->db->get("t_mtr_program");
    return $query->result();
  }

  function channelActivePrograms($channel_id) {
    $this->db->select("program_id");
    $query = $this->db->get_where("t_mtr_channel_program",array("channel_id" => $channel_id));
    return $query->result();
  }

  /**
   * kondisi true adalah
   * if(isset($oId)):
   *    $qty1 = 0
   *    $qty2 = 1
   * else 
   *    $qty1 = 0
   *    $qty2 = 0
   */
  public function eXst($param, $oId = null)
  {
    $this->db->where('eload_number', $param);
    $this->db->where('outlet_id != ', $oId);
    $this->db->from('t_mtr_outlet_eload');
    $qty1 =  $this->db->count_all_results(); 

    $this->db->where('eload_number', $param);
    $this->db->from('t_mtr_outlet_eload');
    $qty2 =  $this->db->count_all_results(); 

    if(($qty1 == 0 && $qty2 == 1) OR ($qty1 == 0 && $qty2 == 0))
      return true;
    else
      return false;
  }

  function generate_outlet_id($outlet_name, $cluster_id, $sales_id, $address, $city, $bFocus) {
    $outlet_name = str_replace("'", "''", $outlet_name);
    $address     = str_replace("'", "''", $address);
    $city        = str_replace("'", "''", $city);
    $this->db->select("sp_mobile_outlet('#', '$sales_id', '$outlet_name', $cluster_id, '$address', '$city', '$bFocus', '0', '0', '0', '0' ) as outlet_id", false);
    $query = $this->db->get();

    // echo $this->db->last_query();
    if($query->num_rows() > 0) 
      { return $query->row()->outlet_id; }
    else
      { return array(); }
  }
 
  /**
   * Untuk menyimpan data territory, priksa dulu apakah sudah ada data di tabel unapproved
   * kalau ada maka update data tersebut. Tapi kalau tidak ada, ambil data dari t_mtr_territory 
   * kemudian simpan ke t_mtr_territory_unapproved
   * @param  Array  $data      [description]
   * @param  string $outlet_id [description]
   * @return null
   */
  public function save_territory($data, $outlet_id) {
    $this->db->select('territory_id');
    $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$outlet_id')", false);
    $query  = $this->db->get('t_mtr_territory_unapproved');
    $data['updated_on'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('username');
    if($query->num_rows() > 0) { 
      $uExist = $query->num_rows();
      $territory_id = $query->row()->territory_id; 
      $this->db->where('territory_id', $territory_id);
      $this->db->update('t_mtr_territory_unapproved', $data);
      $this->mglobal->saveLog('Simpan data Outlet', 'Update Territory; Data='. json_encode($data) , $this->db->insert_id() );
      return $territory_id;
    }
    else { 
      $this->db->select('territory_id');
      $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$outlet_id')", false);
      $query  = $this->db->get('t_mtr_territory');
      $aExist = $query->num_rows();
      $data["territory_id"] = $query->row()->territory_id; 
      $this->db->insert('t_mtr_territory_unapproved', $data);
      $this->mglobal->saveLog('Simpan data Territory', 'New Territory; Data='. json_encode($data) , $this->db->insert_id() );
      return $data["territory_id"];
    }
  } // end of save_territory

  /**
   * Sama halnya dengan menyimpan data territory
   * @param  Array  $data      [description]
   * @param  string $outlet_id [description]
   * @return null
   */
  public function save_outlet($data, $outlet_id) {
    $this->db->where("outlet_id", "'$outlet_id'", false);
    $query  = $this->db->get('t_mtr_outlet_unapproved');
    $data['updated_on'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('username');
    if($query->num_rows() > 0) { 
      // $uExist = $query->num_rows();
      $this->db->where("outlet_id", "'$outlet_id'", false);
      $this->db->update('t_mtr_outlet_unapproved', $data);
    }
    else { 
      $this->db->where("outlet_id", "'$outlet_id'", false);
      $query  = $this->db->get('t_mtr_outlet');
      // $aExist = $query->num_rows();
      $data["territory_id"] = $query->row()->territory_id; 
      $this->db->insert('t_mtr_outlet_unapproved', $data);
    }
    if($data['istatus'] == 0 OR $data['istatus'] == 2 )
      { $log_action = 'New Outlet'; }
    else
      { $log_action = 'Edit Outlet'; }
    $this->mglobal->saveLog('Simpan data Outlet', $log_action.' Data='. json_encode($data) , $outlet_id);
  } // end of save_outlet

  /**
   * Method berikut ini digunakan untuk menyimpan record ke table unapproved:
   * - t_mtr_outlet_data_unapproved
   * - t_mtr_outlet_owner_unapproved
   * - t_mtr_outlet_branding_unapproved
   * @param  [type] $data       [description]
   * @param  [type] $table_name [description]
   * @param  [type] $outlet_id  [description]
   * @return [type]             [description]
   */
  public function saveDatas($data, $table_name, $outlet_id)
  {
    $this->db->where('outlet_id', $outlet_id);
    $query = $this->db->get($table_name);
    $data['outlet_id'] = $outlet_id;
    if($query->num_rows() > 0) { 
      $this->db->update($table_name, $data); 
      $action = 'update_';
    }
    else { 
      $this->db->insert($table_name, $data); 
      $action = 'insert_new_';
    }
    $this->mglobal->saveLog('Simpan data Outlet', $action.$table_name.' Data='. json_encode($data) , $outlet_id);
  }

  /**
   * Proses simpan program outlet (Program HO/Program Cluster).
   * Jika ada checkbox yg dicentang maka hapus dulu semua record program untuk outlet ID ybs,
   * kemudian buat satu variable array $data, agar semua rogram dapat disimpan dengan metoda insert_batch
   * @param  array  $program_id ID dari program yang akan disimpan
   * @param  string $outlet_id  ID Outlet
   */
  public function savePrograms($program_id, $outlet_id) {
    if(count($program_id) != 0) {
      $this->db->delete('t_mtr_channel_program', array('channel_id' => $outlet_id)); 
      foreach ($program_id as $key) {
        $data = array(
          "channel_id" => $outlet_id, 
          "program_id" => $key, 
          "created_by" => $this->session->userdata("username"), 
          "updated_by" => $this->session->userdata("username"),
          "created_on" => date('Y-m-d H:i:s'),
          "updated_on" => date('Y-m-d H:i:s')
        );
        $this->db->insert("t_mtr_channel_program", $data );
        $this->mglobal->saveLog('Simpan data Outlet', 'Outlet Program; Data='. json_encode($data) , $outlet_id);
      }
    }
  }

  public function saveEload($data, $channel_id) {
    $this->db->delete('t_mtr_outlet_eload_unapproved', array('outlet_id' => $channel_id));
    $this->db->insert_batch('t_mtr_outlet_eload_unapproved', $data);
    $this->mglobal->saveLog('Simpan data Outlet', 'Nomor Eload; Data='. json_encode($data) , $channel_id);
  }

  /**
   * Sequence untuk approve data outlet ini sudah diseting sedemikian rupa. Jadi, JANGAN DIUBAH !!!
   * data yang diapprove diambil dari table unapproved berdasarkan outlet_id. 
   * 1. Record dari table unapprove ditampung dalam satu variable array dengan index merepresentasikan nama table masing2
   * 2. Kemudian setiap index kita simpan (update/insert) ke table masternya masing-masing
   * 3. Setelah proases simpan selesai, hapus record yang ada di table unapproved. Record harus benar2 dihapus untuk
   * @param string $outlet_id
   */
  public function approve($outlet_id)
  {
    // jika proses inputnya benar, maka ditable unapprove hanya ada satu row saja dengan key yang sesuai
    $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$outlet_id')", false);
    $data['t_mtr_territory']         = $this->db->get('t_mtr_territory_unapproved')->row_array();
    $data['t_mtr_outlet']            = $this->db->get_where('t_mtr_outlet_unapproved', array('outlet_id' => $outlet_id))->row_array();
    $data['t_mtr_outlet_data']       = $this->db->get_where('t_mtr_outlet_data_unapproved', array('outlet_id' => $outlet_id))->row_array();
    $data['t_mtr_outlet_owner']      = $this->db->get_where('t_mtr_outlet_owner_unapproved', array('outlet_id' => $outlet_id))->row_array();
    $data['t_mtr_outlet_branding']   = $this->db->get_where('t_mtr_outlet_branding_unapproved', array('outlet_id' => $outlet_id))->row_array();
    $data['t_mtr_outlet_frontliner'] = $this->db->get_where('t_mtr_outlet_frontliner_unapproved', array('outlet_id' => $outlet_id))->row_array();
    // $data['t_mtr_bank_account']      = $this->db->get_where('t_mtr_bank_account_unapproved', array())->row();

    foreach ($data as $key => $val) {
      if(count($val) != 0)
        { $this->_approveData($key, $val); }
    }
    $this->_approveEload($outlet_id);
    $this->_removeOldData($outlet_id);
    $this->mglobal->saveLog('Approval data Outlet', 'Approve', $outlet_id);
  }

  /**
   * setiap $data yang akan di approve mengandung index "id", sedangkan di table masternya index tersebut tidak ada
   * maka $data["id"] akan kita hapus kemudian $data["istatus"] kita set menjadi '1'
   * @param string   $table   Nama table
   * @param array()  $data    Record dari table unapprove
   */
  public function _approveData($table, $data)
  {
    foreach ($data as $key => $val) {
      if($val == '' OR is_null($val) OR trim($val) == '-') {
        unset($data[$key]);
      }
    }
    $data['istatus'] = 1;
    $data['updated_on'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('username');
    unset($data['id']); unset($data["outlet_branding_id"]);
    unset($data["outlet_owner_id"]); unset($data["outlet_data_id"]);

    if($table == 't_mtr_territory') {
      $where = array('territory_id' => $data["territory_id"]);
      $query = $this->db->get_where($table, $where )->row_array();
    }
    else {
      $where = array('outlet_id' => $data["outlet_id"]);
      $query = $this->db->get_where($table, $where )->row_array();
    }

    if($query)
    {
      unset($query["id"]);
      $this->db->where($where);
      $this->db->update($table, $data);
    }
    else {
      $this->db->insert($table, $data);
    }
  }

  public function _approveEload($outlet_id)
  {
    $eloadNew = $this->db->get_where('t_mtr_outlet_eload_unapproved', array('outlet_id' => $outlet_id))->result_array();
    $datax = array();
    foreach ($eloadNew as $key) {
      unset($key['eload_id']);
      $datax[]=$key;
    }
    if(count($datax) != 0)
      { 
        $this->db->where('outlet_id', $outlet_id);
        $this->db->delete('t_mtr_outlet_eload');
        $this->db->insert_batch('t_mtr_outlet_eload', $datax); 
      }
  }

  public function reject($outlet_id) {
    $this->_removeOldData($outlet_id);
    $this->mglobal->saveLog('Approval data Outlet', 'Reject', $outlet_id);
  }

  public function delete_outlet($outlet_id) {
    $data = array('istatus' => -5);
    $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$outlet_id')", false);
    $this->db->update('t_mtr_territory',$data);
    $this->db->update('t_mtr_outlet', $data, array('outlet_id' => $outlet_id) );
    $this->db->update('t_mtr_outlet_data', $data, array('outlet_id' => $outlet_id) );
    $this->db->update('t_mtr_outlet_owner', $data, array('outlet_id' => $outlet_id) );
    $this->db->update('t_mtr_outlet_branding', $data, array('outlet_id' => $outlet_id) );
    $this->db->update('t_mtr_outlet_frontliner', $data, array('outlet_id' => $outlet_id) );
    $this->db->delete('t_mtr_outlet_eload', $data, array('outlet_id' => $outlet_id) );
    $this->mglobal->saveLog('Simpan data Outlet', 'Delete; iStatus=-5', $outlet_id);
  }

  private function _removeOldData($outlet_id) {
    $this->db->where("territory_id", "f_get_territory_id_by_channel_id('$outlet_id')", false);
    $this->db->delete('t_mtr_territory_unapproved');
    $this->db->delete('t_mtr_outlet_unapproved', array('outlet_id' => $outlet_id) );
    $this->db->delete('t_mtr_outlet_data_unapproved', array('outlet_id' => $outlet_id) );
    $this->db->delete('t_mtr_outlet_owner_unapproved', array('outlet_id' => $outlet_id) );
    $this->db->delete('t_mtr_outlet_branding_unapproved', array('outlet_id' => $outlet_id) );
    $this->db->delete('t_mtr_outlet_frontliner_unapproved', array('outlet_id' => $outlet_id) );
    $this->db->delete('t_mtr_outlet_eload_unapproved', array('outlet_id' => $outlet_id) );
    // $this->db->delete('t_mtr_bank_account_unapproved', array('outlet_id' => $outlet_id) );
  }

  public function getButtons($menu_id, $user_group_id) {
    $this->db->select("COALESCE (menu_add, 0) AS menu_add, COALESCE (menu_view, 0) AS menu_view, COALESCE (menu_edit, 0) AS menu_edit", false);
    $this->db->select("COALESCE (menu_delete, 0) AS menu_delete, COALESCE (menu_approve, 0) AS menu_approve, COALESCE (menu_reject, 0) AS menu_reject", false);
    $this->db->where("user_group_id", $user_group_id);
    $this->db->where("menu_id", $menu_id);
    $query = $this->db->get("t_mtr_user_role");

    if($query->num_rows() > 0) {
      $result = $query->row_array();
    }
    else {
      $result['menu_add']     = 0;
      $result['menu_view']    = 0;
      $result['menu_edit']    = 0;
      $result['menu_delete']  = 0;
      $result['menu_reject']  = 0;
      $result['menu_approve'] = 0;
    }
    return  $result;
  }

  public function save_frontliner($data, $action) {
    $this->db->trans_begin();

      if($action == 'add') {
        $data['frontliner_code'] = $this->_generate_fo_code($data['frontliner_type']);
        $this->db->insert('t_mtr_outlet_frontliner', $data);
      }
      else {
        $this->db->where('outlet_id', $data["outlet_id"]);
        $this->db->update('t_mtr_outlet_frontliner', $data);
      }

      $this->mglobal->saveLog('Outlet frontliner',$action,'header: '.$data["outlet_id"]);

    if ($this->db->trans_status() === FALSE) { 
      $this->db->trans_rollback(); 
      return false;
    }
    else { 
      $this->db->trans_commit(); 
      return true;
    }
  }

  public function _generate_fo_code($fo_type) {
    if($fo_type == 'SSC') {
      $this->db->select("concat('SSC', cast(max(right(frontliner_code,5)) as integer) +1) as fo_code", false);
      $this->db->where("left(frontliner_code, 3) = 'SSC' ");
    }
    else {
      $this->db->select("concat('FA', cast(max(substring(frontliner_code,3)) as integer) +1) as fo_code", false);
      $this->db->where_not_in("left(frontliner_code, 3)", array('FAA', 'FAB', 'FAC', 'FAD','FAE', 'CAA', 'SSC'), false);
    }
    $query = $this->db->get('t_mtr_outlet_frontliner');
    $data = $query->row()->fo_code;
    return $data ;
  }

  public function get_region() {
    $this->db->select('territory_id, territory_name');
    $query = $this->db->get_where('t_mtr_territory', array('parent_id' => 1));
    return $query->result(); 
  }

  public function get_cluster($territory_id) {
    $this->db->select('territory_id, territory_name');
    $this->db->order_by('territory_name', 'asc');
    $query = $this->db->get_where('t_mtr_territory', array('parent_id' => $territory_id));
    return $query->result(); 
  }

  public function load_outlet($cluster_id, $keyword)
  {
    $where = "tt.parent_id = $cluster_id AND (upper(oo.outlet_id) like '$keyword%' OR UPPER(tt.territory_name) like '%$keyword%')";

    $this->db->select('tt.territory_id, oo.outlet_id, tt.territory_name, uu.user_id, uu.user_name');
    $this->db->select('f_get_group_name(uu.user_group_id) as user_group_name');
    $this->db->join('t_mtr_outlet oo', 'oo.territory_id = tt.territory_id');
    $this->db->join('t_mtr_user uu', 'uu.user_id = tt.user_id');
    $this->db->where($where);
    $query = $this->db->get('t_mtr_territory tt');
    return $query->result();
  }

  public function valid_outlet_category($channel_id)
  {
    $this->db->from('t_mtr_channel_program cp');
    $this->db->join('t_mtr_program pp', 'pp.program_id = cp.program_id', 'left');
    $this->db->where('cp.channel_id', $channel_id);
    $this->db->where('pp.program_type_id', 5);
    return $this->db->count_all_results();
  }

  public function save_category($data)
  {
    $this->db->trans_begin();

    $this->db->insert_batch('t_mtr_channel_program', $data);
    foreach ($data as $key) {
      $this->mglobal->saveLog('Outlet Category','Add; Data:'.json_encode($key), $key["channel_id"]);
    }

    if ($this->db->trans_status() === FALSE)
    {
      $this->db->trans_rollback();
      return false;
    }
    else
    {
      $this->db->trans_commit();
      return true;
    }
  }

  public function delete_outlet_category($id)
  {
    $this->db->trans_begin();

    $this->db->delete('t_mtr_channel_program', array('channel_program_id'=> $id));
    $this->mglobal->saveLog('Outlet Category','Delete', $id);

    if ($this->db->trans_status() === FALSE)
    {
      $this->db->trans_rollback();
      return false;
    }
    else
    {
      $this->db->trans_commit();
      return true;
    }
  }

  public function addNewProgram($program_type_id, $info) { // $program_type_id harus dikirim dari jQuery object button "Simpan"
    $user_id = $this->session->userdata("username");
    $this->db->select(" f_get_cluster( '$user_id') ");
    $cluster = $this->db->get()->row()->f_get_cluster;
    // $caption = $this->input->post("program_name");
    // $description = $this->input->post("program_description");
    $active = $this->input->post("active");

      $data = array(
        "caption" => $info['caption'], 
        "description" => $info['description'], 
        "program_type_id" => $program_type_id,
        "territory_id" => $cluster,
        "created_by" => $user_id,
        "created_on" => date('Y-m-d H:i:s'),
        "updated_by" => $user_id,
        "istatus" =>1
        );

    $this->db->trans_start();
      $this->db->insert("t_mtr_program", $data );
      $program_id = $this->db->insert_id();
      $this->mglobal->saveLog('outlet-program', 'add/update' , json_encode(array('program_id' => $program_id, 'data' => $data)));
    if ($this->db->trans_status() === FALSE)
      $this->db->trans_rollback();
    else
      $this->db->trans_commit();
    return $lastId;
  }
}

/* End of file moutlet.php */
/* Location: ./application/models/moutlet.php */
